Karura

Row

KSM analysis based on 13513 users

  1. None = Default
  2. KSM on Karura but no LKSM = Karura KSM Balance > 0
  3. LKSM Balance = LKSM Balance > 0
  4. LKSM Valult User = depositAmount_LKSM > 0
  5. LKSM LP User = LP depositAmount_LKSM > 0
KSM_Status N Percent (%)
  1. None
3024 22.4
  1. KSM on Karura but no LKSM
5037 37.3
  1. LKSM Balance
3450 25.5
  1. LKSM Vault User
1944 14.4
  1. LKSM LP User
58 0.4

Row

AUSD (on Karura) analysis based on 13513 users

  1. None = Default
  2. AUSD Vault Open = depositAmount_KSM > 0 OR depositAmount_LKSM > 0
  3. Vault open but no AUSD minted = depositAmount_KSM > 0 OR depositAmount_LKSM > 0 AND (is.na(debitAmount_KSM) AND is.na(debitAmount_LKSM)
AUSD_Status N Percent (%)
  1. None
8497 62.9
  1. AUSD Holder
2896 21.4
  1. AUSD Vault Open
1967 14.6
  1. Vault open but no AUSD minted
153 1.1

Acala

Row

DOT analysis

  1. None = Default
  2. DOT on Acala but no LDOT = Acala DOT Balance > 0
  3. LDOT Balance = LDOT Balance > 0
  4. LKSM Vault User = depositAmount_LDOT > 0
  5. LKSM LP User = LP depositAmount_LDOT > 0
#> Error in .checkTypos(e, names_x) : 
#>   Object 'depositAmount_LDOT' not found. Perhaps you intended lp_depositAmount_DOT, lp_depositAmount_LCDOT, lp_depositAmount_LDOT, lp_depositAmount_tDOT
DOT_Status N Percent (%)
  1. None
8966 47.3
  1. DOT on Acala but no LDOT
8508 44.9
  1. LDOT Balance
893 4.7
  1. LDOT LP User
587 3.1

Row

AUSD (on Acala) analysis

  1. None = Default
  2. AUSD Holder = AUSD Balance > 0
  3. AUSD Vault Open = depositAmount_KSM > 0 OR depositAmount_LKSM > 0
  4. Vault open but no AUSD minted = depositAmount_KSM > 0 OR depositAmount_LKSM > 0 AND (is.na(debitAmount_KSM) AND is.na(debitAmount_LKSM)
#> Error in .checkTypos(e, names_x) : 
#>   Object 'depositAmount_DOT' not found. Perhaps you intended lp_depositAmount_DOT, lp_depositAmount_LCDOT, lp_depositAmount_LDOT, lp_depositAmount_tDOT
#> Error in .checkTypos(e, names_x) : 
#>   Object 'depositAmount_DOT' not found. Perhaps you intended lp_depositAmount_DOT, lp_depositAmount_LCDOT, lp_depositAmount_LDOT, lp_depositAmount_tDOT
AUSD_Status N Percent (%)
  1. None
18806 99.2
  1. AUSD Holder
148 0.8

Row

ACA analysis

  1. None = Default
  2. ACA Staker = depositAmount_ACA > 0
  3. No ACA Staking = ACA Balance > 0
  4. No ACA = is.na(ACA Balance)
ACA_Status N Percent (%)
  1. No ACA Staking
569 3
  1. No ACA
18385 97

Methodologh

---
title: "Acala / Karura Staking Dashboards"
output:
  flexdashboard::flex_dashboard:
    orientation: rows
    vertical_layout: scroll
    social: menu
    source_code: embed
---

```{css custom1, echo=FALSE}
.dataTables_scrollBody {
    max-height: 100% !important;
}
```

```{r global, include=FALSE}
library(knitr)

knitr::opts_chunk$set(
  message = FALSE,
  warning = FALSE,
  comment = "#>"
)


library(kableExtra)
library(formattable)
library(lubridate)
library(flexdashboard)
library(DT)
library(subscanr)
library(ghql)
x <- GraphqlClient$new()

# Helper function to concat
`%+%` <- function(a, b) paste0(a, b)

```

```{r tokens, cache = TRUE, include=FALSE}

endpage <- 100 # 2e9

KSM_tokens <- getAccountBalance_acala_token("karura", window = 1, filter = 'filter: {tokenId: {in: ["KSM","LKSM"]}, total: {greaterThan: "0"}} ', endpage = endpage)
KSM_token_wide <- dcast(KSM_tokens, accountId ~ tokenId, value.var = 'total')
# sum(KSM_token_wide$LKSM > 0, na.rm = TRUE)

DOT_tokens <- getAccountBalance_acala_token("acala", window = 1, filter = 'filter: {tokenId: {in: ["DOT","LDOT"]}, total: {greaterThan: "0"}} ', endpage = endpage)
DOT_token_wide <- dcast(DOT_tokens, accountId ~ tokenId, value.var = 'total')
# sum(DOT_token_wide$LDOT> 0, na.rm = TRUE)

```

```{r balances, cache = TRUE, include=FALSE}

aUSD_karura <- getAccountBalance_acala_token("karura", window = 1, filter = 'filter: {tokenId: {in: ["ACA","KUSD","AUSD"]}, total: {greaterThan: "0"}} ', endpage = endpage)
aUSD_karura_wide <- dcast(aUSD_karura, accountId ~ tokenId, value.var = 'total')

aUSD_acala <- getAccountBalance_acala_token("acala", window = 1, filter = 'filter: {tokenId: {in: ["ACA","KUSD","AUSD"]}, total: {greaterThan: "0"}} ', endpage = endpage)
aUSD_acala_wide <- dcast(aUSD_acala, accountId ~ tokenId, value.var = 'total')

```

```{r loans, cache = TRUE, include=FALSE}

loans_acala <- getLoansPositions_acala_loan("acala", window = 1, filter = 'filter: {collateralId: {in: ["aUSD","kUSD","ACA","DOT","LDOT","KSM","LKSM"]}} ', endpage = endpage)
loans_acala_wide <- dcast(loans_acala, owner.id ~ collateral.id, value.var = c('debitAmount','depositAmount'))
# sum(loans_acala_wide$depositAmount_LDOT > 0, na.rm = TRUE)

loans_karura <- getLoansPositions_acala_loan("karura", window = 1, filter = 'filter: {collateralId: {in: ["aUSD","kUSD","ACA","DOT","LDOT","KSM","LKSM"]}} ', endpage = endpage)
loans_karura_wide <- dcast(loans_karura, owner.id ~ collateral.id, value.var = c('debitAmount','depositAmount'))
# sum(loans_karura_wide$depositAmount_LKSM > 0, na.rm = TRUE)

```

```{r daily, cache = TRUE, include=FALSE}

lp_acala <- getLoansDailyPositions_acala_loan("acala", window=10)
lp_acala[, depositAmount := as.numeric(depositAmount)]
lp_acala[, debitAmount := as.numeric(debitAmount)]
lp_acala_wide <- dcast(lp_acala, owner.id ~ collateral.id, value.var = c('debitAmount','depositAmount'), fun.aggregate = sum)
setnames(lp_acala_wide, names(lp_acala_wide), "lp_" %+% names(lp_acala_wide))

lp_karura <- getLoansDailyPositions_acala_loan("karura", window=1)
lp_karura[, depositAmount := as.numeric(depositAmount)]
lp_karura[, debitAmount := as.numeric(debitAmount)]
lp_karura_wide <- dcast(lp_karura, owner.id ~ collateral.id, value.var = c('debitAmount','depositAmount'), fun.aggregate = sum)
setnames(lp_karura_wide, names(lp_karura_wide), "lp_" %+% names(lp_karura_wide))

```

```{r merge}


karura_addr <- merge(KSM_token_wide, aUSD_karura_wide, by = 'accountId', all = TRUE)
karura_addr <- merge(karura_addr, loans_karura_wide, by.x = 'accountId', by.y = 'owner.id', all = TRUE)
karura_addr <- merge(karura_addr, lp_karura_wide, by.x = 'accountId', by.y = 'lp_owner.id', all = TRUE)
if (FALSE) {
  sum(karura_addr$LKSM > 0, na.rm = TRUE)
  sum(karura_addr$depositAmount_LKSM > 0, na.rm = TRUE)
  sum(karura_addr$lp_depositAmount_LKSM > 0, na.rm = TRUE)
  sum(karura_addr$LKSM > 0 | karura_addr$depositAmount_LKSM > 0 | karura_addr$lp_depositAmount_LKSM > 0, na.rm = TRUE)
}


acala_addr <- merge(DOT_token_wide, aUSD_acala_wide, by = 'accountId', all.x = TRUE)
acala_addr <- merge(acala_addr, loans_acala_wide, by.x = 'accountId', by.y = 'owner.id', all = TRUE)
acala_addr <- merge(acala_addr, lp_acala_wide, by.x = 'accountId', by.y = 'lp_owner.id', all = TRUE)
if (FALSE) {
  sum(acala_addr$LDOT > 0, na.rm = TRUE)
  sum(acala_addr$depositAmount_LDOT > 0, na.rm = TRUE)
  sum(acala_addr$lp_depositAmount_LDOT > 0, na.rm = TRUE)
  sum(acala_addr$LDOT > 0 | acala_addr$depositAmount_LDOT > 0 | acala_addr$lp_depositAmount_LDOT > 0, na.rm = TRUE)
}

```

# Karura {.tabset}

Row
----

### KSM analysis based on `r karura_addr[, .N]` users

1) None = Default
2) KSM on Karura but no LKSM = Karura KSM Balance > 0
3) LKSM Balance = LKSM Balance > 0
4) LKSM Valult User = depositAmount_LKSM > 0
5) LKSM LP User = LP depositAmount_LKSM > 0

```{r KSM}

ksm_n <- karura_addr[, .N]
karura_addr[, KSM_Status := '1) None']
karura_addr[KSM > 0, KSM_Status := '2) KSM on Karura but no LKSM']
karura_addr[LKSM > 0, KSM_Status := '3) LKSM Balance']
karura_addr[depositAmount_LKSM > 0, KSM_Status := '4) LKSM Vault User']
karura_addr[lp_depositAmount_LKSM > 0, KSM_Status := '5) LKSM LP User']
ksm_tbl <- karura_addr[, .N, by = 'KSM_Status'] %>%
  setorder(KSM_Status)
ksm_tbl2 <- karura_addr[, 100*round(.N / ksm_n, 3), by = 'KSM_Status'] %>%
  setnames("V1", "Percent (%)") %>%
  setorder(KSM_Status)
ksm_tbl <- merge(ksm_tbl, ksm_tbl2)

knitr::kable(ksm_tbl, escape = FALSE, align = c("l",rep("r",3))) %>%
  kable_styling()

```

Row
----

### AUSD (on Karura) analysis based on `r karura_addr[, .N]` users

1) None = Default
2) AUSD Vault Open = depositAmount_KSM > 0 OR depositAmount_LKSM > 0
3) Vault open but no AUSD minted = depositAmount_KSM > 0 OR depositAmount_LKSM > 0 AND (is.na(debitAmount_KSM) AND is.na(debitAmount_LKSM)

```{r KUSD}

# aUSD
# No aUSD
# aUSD holder
# aUSD vault open
# Vault open but no aUSD minted
ausd_karura_n <- karura_addr[, .N]
karura_addr[, AUSD_Status := '1) None']
karura_addr[KUSD > 0, AUSD_Status := '2) AUSD Holder']
karura_addr[depositAmount_KSM > 0 | depositAmount_LKSM > 0, AUSD_Status := '3) AUSD Vault Open']
karura_addr[depositAmount_KSM > 0 | depositAmount_LKSM > 0 & (is.na(debitAmount_KSM) & is.na(debitAmount_LKSM)), AUSD_Status := '4) Vault open but no AUSD minted']
ausd_tbl <- karura_addr[, .N, by = 'AUSD_Status'] %>%
  setorder(AUSD_Status)
ausd_tbl2 <- karura_addr[, 100*round(.N / ausd_karura_n, 3), by = 'AUSD_Status'] %>%
  setnames("V1", "Percent (%)") %>%
  setorder(AUSD_Status)

ausd_tbl <- merge(ausd_tbl, ausd_tbl2)

knitr::kable(ausd_tbl, escape = FALSE, align = c("l",rep("r",3))) %>%
  kable_styling()

```


# Acala {.tabset}

Row
----

### DOT analysis 

1) None = Default
2) DOT on Acala but no LDOT = Acala DOT Balance > 0
3) LDOT Balance = LDOT Balance > 0
4) LKSM Vault User = depositAmount_LDOT > 0
5) LKSM LP User = LP depositAmount_LDOT > 0

```{r DOT}

dot_n <- acala_addr[, .N]
acala_addr[, DOT_Status := '1) None']
acala_addr[DOT > 0, DOT_Status := '2) DOT on Acala but no LDOT']
acala_addr[LDOT > 0, DOT_Status := '3) LDOT Balance']
try(acala_addr[depositAmount_LDOT > 0, DOT_Status := '4) LDOT Vault User'])
acala_addr[lp_depositAmount_LDOT > 0, DOT_Status := '5) LDOT LP User']
dot_tbl <- acala_addr[, .N, by = 'DOT_Status'] %>%
  setorder(DOT_Status)
dot_tbl2 <- acala_addr[, 100*round(.N / dot_n, 3), by = 'DOT_Status'] %>%
  setnames("V1", "Percent (%)") %>%
  setorder(DOT_Status)

dot_tbl <- merge(dot_tbl, dot_tbl2)

knitr::kable(dot_tbl, escape = FALSE, align = c("l",rep("r",3))) %>%
  kable_styling()

```

Row
----

### AUSD (on Acala) analysis 

1) None = Default
2) AUSD Holder = AUSD Balance > 0
3) AUSD Vault Open = depositAmount_KSM > 0 OR depositAmount_LKSM > 0
4) Vault open but no AUSD minted = depositAmount_KSM > 0 OR depositAmount_LKSM > 0 AND (is.na(debitAmount_KSM) AND is.na(debitAmount_LKSM)

```{r AUSD}

# aUSD
# No aUSD
# aUSD holder
# aUSD vault open
# Vault open but no aUSD minted

ausd_acala_n <- acala_addr[, .N]
acala_addr[, AUSD_Status := '1) None']
acala_addr[AUSD > 0, AUSD_Status := '2) AUSD Holder']
try(acala_addr[depositAmount_ACA > 0 | depositAmount_DOT > 0 | depositAmount_LDOT > 0, AUSD_Status := '3) AUSD Vault Open'])
try(acala_addr[depositAmount_ACA > 0 | depositAmount_DOT > 0 | depositAmount_LDOT > 0 & (is.na(debitAmount_ACA) & is.na(debitAmount_DOT) & is.na(debitAmount_LDOT)), AUSD_Status := '4) Vault open but no AUSD minted'])
ausd_tbl <- acala_addr[, .N, by = 'AUSD_Status'] %>%
  setorder(AUSD_Status)
ausd_tbl2 <- acala_addr[, 100*round(.N / ausd_acala_n, 3), by = 'AUSD_Status'] %>%
  setnames("V1", "Percent (%)") %>%
  setorder(AUSD_Status)

ausd_tbl <- merge(ausd_tbl, ausd_tbl2)

knitr::kable(ausd_tbl, escape = FALSE, align = c("l",rep("r",3))) %>%
  kable_styling()

```

Row
----

### ACA analysis 

1) None = Default
2) ACA Staker = depositAmount_ACA > 0
3) No ACA Staking = ACA Balance > 0 
4) No ACA = is.na(ACA Balance)

```{r ACA}

# names(acala_addr)
# summary(acala_addr$debitAmount_ACA)
# summary(acala_addr$depositAmount_ACA)
# summary(loans_acala_wide$debitAmount_ACA)
# summary(loans_acala_wide$depositAmount_ACA)
# 
# tmp <- loans_acala_wide[, .(ownerId, debitAmount_ACA,depositAmount_ACA)]
# tmp <- merge(tmp, acala_addr[, .(accountId, ACA, ACA_Status)], by.x = "ownerId", by.y="accountId", all = TRUE)

                        
# ACA
# ACA staker
# No ACA staking
# No ACA
aca_n <- acala_addr[, .N]
acala_addr[, ACA_Status := '1) None']
acala_addr[depositAmount_ACA > 0, ACA_Status := '2) ACA Staker']
acala_addr[ACA > 0 && is.na(depositAmount_ACA), ACA_Status := '3) No ACA Staking']
acala_addr[is.na(ACA) | ACA == 0, ACA_Status := '4) No ACA']
aca_tbl <- acala_addr[, .N, by = 'ACA_Status'] %>%
  setorder(ACA_Status)
aca_tbl2 <- acala_addr[, 100*round(.N / aca_n, 3), by = 'ACA_Status'] %>%
  setnames("V1", "Percent (%)") %>%
  setorder(ACA_Status)

aca_tbl <- merge(aca_tbl, aca_tbl2)

knitr::kable(aca_tbl, escape = FALSE, align = c("l",rep("r",2)) ) %>%
  kable_styling()


```

# Methodologh {.tabset}

* The Subquery Network _Acala Tokens_ project was used to get balances on Acala for DOT, LDOT, ACA, and AUSD and the *Karura Tokens* project was used for Karura for KSM, LKSM, and AUSD.
  - https://explorer.subquery.network/subquery/AcalaNetwork/acala-tokens
  - https://explorer.subquery.network/subquery/AcalaNetwork/karura-tokens
  
* The Subquery Network *Acala Loans* project was used to get deposit and debit balances on Acala for DOT, LDOT, ACA, and AUSD and the *Karura Loans* project was used for Karura for KSM, LKSM, and AUSD.
  - https://explorer.subquery.network/subquery/AcalaNetwork/acala-loans
  - https://explorer.subquery.network/subquery/AcalaNetwork/karura-loan
  
* The methodology for each Token is summarized in that section.

* The Acala / Karura data is as of `r Sys.time()`.